package com.ybkj.o2o.mc.manager.impl;

import com.ybkj.o2o.mc.common.DataForm;
import com.ybkj.o2o.mc.common.ImportTask;
import com.ybkj.o2o.mc.entity.PassengerGrade;
import com.ybkj.o2o.mc.manager.PassengerImportManager;
import com.ybkj.o2o.mc.service.EnterpriseService;
import com.ybkj.o2o.mc.service.PassengerAccountService;
import com.ybkj.o2o.mc.service.PassengerAddressService;
import com.ybkj.o2o.mc.service.PassengerConcatsService;
import com.ybkj.o2o.mc.service.PassengerGradeService;
import com.ybkj.o2o.mc.service.PassengerService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * Created by shine on 15/8/19.
 */
@SuppressWarnings("all")
@Service
public class DefaultPassengerImportManager implements PassengerImportManager {

  @Autowired
  private PassengerService passengerService;

  @Autowired
  private EnterpriseService enterpriseService;

  @Autowired
  private PassengerGradeService passengerGradeService;

  @Autowired
  private PassengerConcatsService passengerConcatsService;

  @Autowired
  private PassengerAddressService passengerAddressService;

  @Autowired
  private PassengerAccountService passengerAccountService;

  @Override
  @Transactional(rollbackFor = Exception.class)
  public DataForm<Map<String, Object>> startImport(String server,
                                                   String dbname,
                                                   String user,
                                                   String password,
                                                   String appKey) {

    DataForm<Map<String, Object>> dataForm = new DataForm<>();
    dataForm.setStatus(0);

    ExecutorService executorService = Executors.newFixedThreadPool(10);

    PreparedStatement pstmt = null;

    Connection connection = null;

    AtomicInteger atomicInteger = new AtomicInteger();

    try {
      Class.forName("com.mysql.jdbc.Driver");

      String jdbc = "jdbc:mysql://" + server + "/" + dbname
              + "?useUnicode=true&characterEncoding=utf-8";

      connection = DriverManager.getConnection(jdbc, user, password);

      PassengerGrade gradecommon = passengerGradeService.findByNameAndAppKey("普通", appKey);

      PassengerGrade gradevip = passengerGradeService.findByNameAndAppKey("VIP", appKey);
      if (null == gradecommon) {
        gradecommon = new PassengerGrade();
        gradecommon.setAppKey(appKey);
        gradecommon.setName("普通");
        gradecommon.setCreated(new Date());
        gradecommon.setUpdated(new Date());
        gradecommon.setVersion(1);
        passengerGradeService.insert(gradecommon);
      }
      if (null == gradevip) {
        gradevip = new PassengerGrade();
        gradevip.setAppKey(appKey);
        gradevip.setName("VIP");
        gradevip.setCreated(new Date());
        gradevip.setUpdated(new Date());
        gradevip.setVersion(1);
        passengerGradeService.insert(gradevip);
      }

      pstmt = connection.prepareStatement("select id,abbreviation from dj_company");
      ResultSet companyRs = pstmt.executeQuery();

      List<Map<String, String>> companyMapList = new ArrayList<>();
      if (null != companyRs) {
        while (companyRs.next()) {
          Map<String, String> companyMap = new HashMap<>();
          companyMap.put("id", companyRs.getString("id"));
          companyMap.put("name", companyRs.getString("abbreviation"));
          companyMapList.add(companyMap);
        }
        companyRs.close();
      }

      if (pstmt != null) {
        pstmt.close();
      }


      pstmt = connection.prepareStatement("select phone from dj_blacklist");
      ResultSet blackRs = pstmt.executeQuery();
      List<String> blackList = new ArrayList<String>();
      while (blackRs.next()) {
        blackList.add(blackRs.getString("phone"));
      }
      if (null != blackRs) {
        blackRs.close();
      }
      if (pstmt != null) {
        pstmt.close();
      }

      try {
        Statement addindex = connection.createStatement();
        if (addindex != null) {
          String sql = "ALTER TABLE `dj_order` ADD INDEX clientPhone ( `clientPhone` )";
          addindex.addBatch(sql);
          sql = "ALTER TABLE `dj_order_finished_info` ADD INDEX clientPhone ( `clientPhone` )";
          addindex.addBatch(sql);
          sql = "ALTER TABLE `dj_phone_record` ADD INDEX memberPhone ( `memberPhone` )";
          addindex.addBatch(sql);
          addindex.executeBatch();

          addindex.close();
          addindex = null;
        }
      } catch (SQLException ex) {
        ex.printStackTrace();
      }

      pstmt = connection.prepareStatement("select COUNT(*) from dj_member");
      ResultSet resultSet = pstmt.executeQuery();
      int total = 0;
      if (resultSet.next()) {
        total = resultSet.getInt(1);
      }
      resultSet.close();
      resultSet = null;
      pstmt.close();
      pstmt = null;
      int limit = 100;
      pstmt = connection.prepareStatement("select * from dj_member limit ?,?");

      for (int offset = 0; offset <= total; offset = offset + limit) {

        pstmt.setInt(1, offset);
        pstmt.setInt(2, limit);

        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
          //逻辑
          Map<String, Object> map = new HashMap<String, Object>();
          ResultSetMetaData rsmd = rs.getMetaData();
          int columnCount = rsmd.getColumnCount();
          for (int i = 1; i <= columnCount; i++) {
            String label = rsmd.getColumnLabel(i);
            Object value = rs.getObject(label);
            map.put(label, value);
          }

          executorService.submit(new ImportTask(gradecommon, gradevip, enterpriseService,
                  companyMapList, map, connection, appKey, blackList, passengerService,
                  passengerConcatsService, passengerAddressService,
                  passengerAccountService, atomicInteger));
        }

        rs.close();

      }

      pstmt.close();
      pstmt = null;

      //捕获驱动加载失败异常
    } catch (ClassNotFoundException ex) {
      dataForm.setStatus(-1);
      ex.printStackTrace();

      //捕获SQL语句执行失败异常
    } catch (SQLException ex) {
      dataForm.setStatus(-1);
      ex.printStackTrace();
      //恢复变量初始值
    }

    return dataForm;
  }


}

